﻿--跟进人员的成单
CREATE PROCEDURE [dbo].[proc_Order_Getlist_GenJin_Kf]
	(
		@UserName nvarchar(50),
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int
	)
AS
begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	;WITH List1 As(Select Id
		,OId
		,OrderId
		,CusId
		,CusName
		,TypeName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,CreateDate
		,YingShouPrice
		,ShiShouPrice
		,IsGreen
		--,IsNull((Select Top 1 IsFinish From Project Where ProductId In(50,51,52) And OrderId=[Order].OId),0) As StateId
,(Case When (Select Count(0) From Project Where ProductId In(select id from Product where flag=1) And OrderId=[Order].OId)=0 Then 0
	When (Select Count(0) From Project Where ProductId In(select id from Product where flag=1) And OrderId=[Order].OId And IsFinish=0)=0 Then 1 
	Else 0 End) As StateId	--该成单是否包含未完成的网站项目
		,IsNull((Select Title From Order_State Where Id=[Order].StateId),'---') As StateName
		,IsNull(TrackKfAccount,'') As TrackKfAccount	--维护客服
,IsNull((Select Top 1 ProductName From Project Where ProductId In(select id from Product where flag=1) And OrderId=[Order].OId),'') As ProductName		--项目名称
,(Select Top 1 ProjectId From Project Where ProductId In(select id from Product where flag=1) And OrderId=[Order].OId) As ProjectId		--项目编号
--,IsNull((Select Sum(SalePrice) From Project Where ProductId In(50,51,52) And OrderId=[Order].OId),0) As YingShouPrice		--网站项目销售价格
,IsNull((Select UserName From ProjectInterfaceSet Where CompanyId=[Order].CompanyId	And ProductId In
(Select Top 1 ProductId From Project Where ProductId In(select id from Product where flag=1) And OrderId=[Order].OId)),'') As UserName		--该成单网站项目的接口人
		,CompanyId
		,ExCompanyId
		From [Order]
		Where GenJinKfAccount=@UserName
		And CreateDate>=@sRq And CreateDate<=@eRq
		And (@Stext='' Or CusName like '%'+@Stext+'%')
	),
	List As (Select ROW_NUMBER() OVER (Order By StateId, CreateDate Desc) As Row,
		* From List1
	)
	
	Select *,(Select Count(0) From List) As RecordCount From List Where Row Between @StartIndex and @EndIndex Order By Row
End
